package heathot.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import heathot.entiy.Book;
import heathot.entiy.BookPager;
import heathot.util.JdbcUtil;

public class BookDaoimpl implements BookDao {

	@Override
	public int addBook(Book book) {
		Connection conn = JdbcUtil.getConnection();
		PreparedStatement pst = null;
		int i = 0;
		String sql ="insert into book(name,author,publish,price) values(?,?,?,?)";
		try {
		    pst = conn.prepareStatement(sql);
			pst.setString(1,book.getName());
			pst.setString(2, book.getAuthor());
			pst.setString(3, book.getPublish());
			pst.setFloat(4, book.getPrice());
			i = pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JdbcUtil.close(conn,pst,null);
		}
		
		return i;
	}

	@Override
	public int deleteBook(int id) {
		Connection conn = JdbcUtil.getConnection();
		PreparedStatement pst = null;
		int i=0;
		String sql ="delete book where id=?";
		try {
			pst = conn.prepareStatement(sql);
			pst.setInt(1,id);
			i=pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JdbcUtil.close(conn,pst,null);
		}
		return i;
	}

	@Override
	public int upadateBook(Book book) {
		Connection conn = JdbcUtil.getConnection();
		PreparedStatement pst = null;
		int i=0;
		try {
			String sql="update book set name=?,author=?,publish=? price=? where id=?";
			pst=conn.prepareStatement(sql);
			pst.setString(1, book.getName());
			pst.setString(2, book.getAuthor());
			pst.setString(3, book.getPublish());
			pst.setFloat(4, book.getPrice());
			i=pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JdbcUtil.close(conn, pst,null);
		}
		return i;
	}

	@Override
	public Book selectBook(int id) {
		Connection conn = JdbcUtil.getConnection();
		PreparedStatement pst = null;
		ResultSet rs = null;
		Book book = null;
		String sql = "select id,name,author,publish,price from book where id=?";
		try {
			pst = conn.prepareStatement(sql);
			pst.setInt(1,id);
			rs=pst.executeQuery();
			while(rs.next()){
			String name = rs.getString("name");
			String author = rs.getString("author");
			String publish = rs.getString("publish");
			float price = rs.getFloat("price");
			book = new Book(id,name,author,publish,price);}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JdbcUtil.close(conn, pst, rs);
		}
		return book;
	}

	@Override
	public List<Book> selectBooks() {
		Connection conn = JdbcUtil.getConnection();
		PreparedStatement pst = null;
		ResultSet rs = null;
		List<Book> books = new ArrayList<Book>();
		String sql ="select * from book";
		try {
			pst = conn.prepareStatement(sql);
			rs = pst.executeQuery();
			while(rs.next()){
				String name = rs.getString("name");
				String author = rs.getString("author");
				String publish = rs.getString("publish");
				float price = rs.getFloat("price");
				int id =rs.getInt("id");
				Book book = new Book(id,name,author,publish,price);
				books.add(book);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JdbcUtil.close(conn, pst, rs);
		}
		return books;
	}

	@Override
	public BookPager selectBookByPage(int currentpage, int pagesize) {
		Connection conn = JdbcUtil.getConnection();
		int totalRecourd =getTotalRecourd();
		int totalPage = (totalRecourd+pagesize-1)/pagesize;
		PreparedStatement pst = null;
		ResultSet rs = null;
		BookPager bp = null;
		List<Book> books = new ArrayList<Book>();
		String sql = "select * from book limit "+currentpage*pagesize+","+pagesize;
		try {
			pst = conn.prepareStatement(sql);
			rs = pst.executeQuery();
			while (rs.next()){
				String name = rs.getString("name");
				String author = rs.getString("author");
				String publish = rs.getString("publish");
				float price = rs.getFloat("price");
				int id = rs.getInt("id");
				Book book = new Book(id,name,author,publish,price);
				books.add(book);
			}
				bp = new BookPager(books,currentpage,totalPage);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			JdbcUtil.close(conn, pst, rs);
		}
		return bp;
	}
	private int getTotalRecourd(){
		Connection conn = JdbcUtil.getConnection();
		PreparedStatement pst = null;
		ResultSet rs = null;
		int totalRecourd = 0;
		String sql = "select count(*)from book";
		try {
			pst= conn.prepareStatement(sql);
			rs = pst.executeQuery();
			if(rs.next()){
				totalRecourd = rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			JdbcUtil.close(conn, pst,rs);
		}
		return totalRecourd;
	}

}
